package com.situ.company.project.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;


import com.situ.company.project.dao.IProjectDao;
import com.situ.company.project.model.ProjectModel;
import com.situ.company.util.JDBCUtil;

public class ProjectDaoImpl implements IProjectDao {

	private String table = "project";
	private Connection conn;
	private PreparedStatement ps;

	@Override
	public Integer insert(ProjectModel model) {
		StringBuffer sql = new StringBuffer(" insert into ");
		sql.append( table ).append( "( code,name,time )" ).append( "values(?,?,?)" );
		
		try {
		conn = JDBCUtil.getConnection();
		ps = conn.prepareStatement(sql.toString());
		ps.setString(1, model.getCode());
		ps.setString(2, model.getName());
		ps.setString(3, model.getTime());
		return ps.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			JDBCUtil.close(conn, ps);
		}
		
		return null;
	}

	@Override
	public Integer deleteByCode(ProjectModel model) {
		
		StringBuffer sql = new StringBuffer( " delete from " );
		sql.append( table ).append( " where " ).append( " code " ).append( " = " ).append("?");
		
		try {
		conn = JDBCUtil.getConnection();
		ps = conn.prepareStatement(sql.toString());
		ps.setString(1, model.getCode());
		return ps.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			JDBCUtil.close(conn, ps);
		}
		
		return null;
	}

	@Override
	public Integer delete(ProjectModel model) {
		
		StringBuffer sql = new StringBuffer(" delete from ");
		sql.append( table );
		List<Object> values = appendWhere(sql,model);
		try {
			conn = JDBCUtil.getConnection();
			ps = conn.prepareStatement(sql.toString());
			for(int i=0;i<values.size();i++) {
				ps.setObject(i+1, values.get(i));
				return ps.executeUpdate();
			}
		} catch (Exception e) {
			
		} finally {
			JDBCUtil.close(conn, ps);
		}
		return null;
	}

	@Override
	public Integer updateAll(ProjectModel model) {
		
		StringBuffer sql = new StringBuffer(" update ");
		sql.append( table ).append(" set name=?,time=? where code=? ");
		
		try {
		conn = JDBCUtil.getConnection();
		ps = conn.prepareStatement(sql.toString());
		ps.setString(1, model.getName());
		ps.setString(2, model.getTime());
		ps.setString(3, model.getCode());
		return ps.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			JDBCUtil.close(conn, ps);
		}
		
		return null;
	}

	@Override
	public Integer updateActive(ProjectModel model) {
		StringBuffer sql = new StringBuffer(" update ");
		sql.append( table ).append( " set id=id,");
		List<Object> values = appendSet(sql, model);
		try {
			conn = JDBCUtil.getConnection();
			ps = conn.prepareStatement(sql.toString());
			for(int i=0;i<values.size();i++) {
				ps.setObject(i+1, values.get(i));
				
				return ps.executeUpdate();
			}
		} catch (Exception e) {
			
		} finally {
			JDBCUtil.close(conn, ps);
		}
		return null;
	}

	@Override
	public List<ProjectModel> selectList(ProjectModel model) {
		StringBuffer sql = new StringBuffer(" select id,");
		sql.append( " code,name,time " ).append( "from " ).append( table );
		List<Object> values = appendWhere(sql,model);
		ResultSet rs =null;
		List<ProjectModel> result = new ArrayList<>();
		try {
			conn=JDBCUtil.getConnection();
			ps = conn.prepareStatement(sql.toString()); 
			for(int i=0;i<values.size();i++) 
				ps.setObject(i+1, values.get(i));
			
			rs =  ps.executeQuery();
			while(rs.next()) {//ORM映射
				ProjectModel m1 = new ProjectModel();
			  m1.setId(rs.getInt("id"));
			  m1.setCode(rs.getString("code"));
			  m1.setName(rs.getString("name"));
			  m1.setTime(rs.getString("time"));
			  result.add(m1);
			}
			
		} catch (Exception e) {
			// TODO: handle exception
		}finally {
			JDBCUtil.close(conn, ps);
		}
		
		return result;
	}

	@Override
	public ProjectModel selectModel(ProjectModel model) {
		StringBuffer sql = new StringBuffer( " select id, " );
		sql.append( " code,name,time  " ).append(" from ").append( table ).append(" where code=?") ;
		
		ResultSet rs =null;
		
		try {
			conn=JDBCUtil.getConnection();
			ps = conn.prepareStatement(sql.toString()); 
			ps.setString(1, model.getCode());
			
			rs =  ps.executeQuery();
			if(rs.next()) {
				ProjectModel m1 = new ProjectModel();
			  m1.setId(rs.getInt("id"));
			  m1.setCode(rs.getString("code"));
			  m1.setName(rs.getString("name"));
			  m1.setTime(rs.getString("time"));
			  return m1;
			}
			
		} catch (Exception e) {
			// TODO: handle exception
		}finally {
			JDBCUtil.close(conn, ps);
		}
		return null;
	}

	@Override
	public Integer selectCount(ProjectModel model) {
		StringBuffer sql = new StringBuffer(" select count(1) from project ");
		List<Object> vals = appendWhere(sql, model);
		return JDBCUtil.queInt(sql.toString(), vals);
	}
	
	private List<Object> appendSet(StringBuffer sql,ProjectModel model){
		List<Object> values = new ArrayList<>();
		if(model.getName()!=null) {
			sql.append("name=?");
		values.add(model.getName());
		}
		if(model.getTime()!=null) {
			sql.append("time=?");
		values.add(model.getTime());
		}
		sql.append("where code=?");
		values.add(model.getCode());
		return values;
	}
	
	
	private List<Object> appendWhere(StringBuffer sql, ProjectModel model) {
		sql.append(" where 1=1 ");
		List<Object> values = new ArrayList<>();
		String code = model.getCode();
		if(code!=null&&!code.isEmpty()) {
			sql.append(" and code like ? ");
			values.add(code);
		}
		String name = model.getName();
		if(name!=null&&!name.isEmpty()) {
			sql.append(" and name like ? ");
			values.add(name);
		}
		String time = model.getTime();
		if(time!=null&&!time.isEmpty()) {
			sql.append(" and time like ? ");
			values.add(time);
	}
		if(model.isPageOn()) {
			sql.append(" limit ?,? ");
			values.add(model.getRowStart());
			values.add(model.getPageLimit());
		}
		return values;
	}
	
}
